﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using BLL.Services.Requests.Listing;

namespace BLL.Services.Requests
{
    public class RequestsCollection
    {
        private readonly string _connectionString;

        public RequestsCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Create(Request request)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Requests.Create(conn, request.Name, request.Duration, request.AudioFile, request.Content, request.DateOfPublish);

                cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int requestId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Requests.Delete(conn, requestId);

                cmd.ExecuteNonQuery();
            }
        }

        public void Save(Request request)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Requests.Update(conn, request.RequestId, request.Name, request.Duration, request.AudioFile, request.Content, request.DateOfPublish);

                cmd.ExecuteNonQuery();
            }
        }

        public Request GetRequestById(int requestId)
        {
            var request = new Request();
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                var cmd = StoredProcedures.Requests.ById(conn, requestId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        request.AudioFile = reader[0] as string;
                        request.Content = reader[1] as string;
                        request.DateOfPublish = reader.GetDateTime(2);
                        request.Duration = reader.GetInt32(3);
                        request.Name = reader[4] as string;
                        request.RequestId = reader.GetInt32(5);
                    }
                    return request;
                }
            }
        }

        public RequestsListing Listing(int page, int size, string column, int direction, RequestsFilter filter)
        {
            var requestsListing = new RequestsListing();
            if (filter == null)
            {
                filter = new RequestsFilter();
            }

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();

                requestsListing.Column = column;
                requestsListing.Direction = direction;
                requestsListing.Filter = filter;
                requestsListing.Page = page;
                requestsListing.Size = size;

                var cmd = StoredProcedures.Requests.Listing(conn, page, size, column, direction, filter.Name);

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    {
                        Direction = ParameterDirection.ReturnValue
                    };
                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var request = new RequestsListing.Request();
                        request.AudioFile = reader[0] as string;
                        request.Content = reader[1] as string;
                        request.DateOfPublish = reader.GetDateTime(2);
                        request.Duration = reader.GetInt32(3);
                        request.Name = reader[4] as string;
                        request.RequestId = reader.GetInt32(5);
                        requestsListing.Requests.Add(request);
                        
                    }
                }

                requestsListing.TotalPages = (int)totalCount.Value % size == 0 ? (int)totalCount.Value / size : ((int)totalCount.Value / size) + 1;
            }
            return requestsListing;
        }

    }
}
